/*
 * #%L
 * SwiftUICommonUtilities
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2011 - 2012 Linkwithweb
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public 
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */
package com.linkwithweb.products.swiftui.export.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author ashwin
 * 
 */
public class ExcelExporter {
	protected static final transient Log log = LogFactory.getLog(ExcelExporter.class);
	// creation of the workbook
	public HSSFWorkbook workbook = new HSSFWorkbook();
	// getting the styles that we need to set to the required cells
	public Map<String, CellStyle> styles = Styles.getStyles(workbook);

	private boolean isDashboardConnection = true;

	private Connection dbConnection = null;

	public ExcelExporter(Connection dbConnection) {
		this.dbConnection = dbConnection;
	}

	public ExcelExporter() {
	}

	public ExcelExporter(boolean isDashboardConnection) {
		this.isDashboardConnection = isDashboardConnection;
	}

	protected Connection getJDBCConnection() {
		return this.dbConnection;
	}

	// an array that stores the width of the column that has the highest length
	// public int columnWidths[];

	/**
	 * @return the dbConnection
	 */
	public Connection getDbConnection() {
		return dbConnection;
	}

	/**
	 * @param dbConnection
	 *            the dbConnection to set
	 */
	public void setDbConnection(Connection dbConnection) {
		this.dbConnection = dbConnection;
	}

	/**
	 * @param reportName
	 * @param rs
	 * @param parameters
	 * @param out
	 */
	public void createExcel(String reportName, ResultSet rs, Map parameters, OutputStream out) {
		Map map = new HashMap();
		try {

			ResultSetMetaData rsm = rs.getMetaData();
			// System.out.println("Statement " + rs.getStatement().toString());
			int count = rsm.getColumnCount();
			// System.out.println(rsm.getTableName(1));
			// giving the sheet a name
			String sheetName = rsm.getTableName(1);
			if (sheetName == null || sheetName.trim().isEmpty()) {
				sheetName = reportName;
			}
			HSSFSheet sheet = workbook.createSheet(sheetName);

			// columnWidths = new int[count];
			// setting the title row
			int rownumber = 0;
			HSSFRow headRow = sheet.createRow(rownumber);
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$G$1"));
			HSSFCell titleCell = headRow.createCell(0);
			Styles.addStyleAndValue(java.sql.Types.VARCHAR, reportName, titleCell);
			titleCell.setCellStyle(styles.get("header"));
			// after adding increment row
			rownumber = rownumber + 4;

			// //////////////////////////

			HSSFRow filterRow = sheet.createRow(rownumber);
			int next = 0;
			Set parameterEntrySet = parameters.entrySet();
			for (Iterator iter = parameterEntrySet.iterator(); iter.hasNext();) {
				Map.Entry pairs = (Map.Entry) iter.next();

				HSSFCell filterName = filterRow.createCell(0);
				filterName.setCellValue((String) pairs.getKey());

				HSSFCell filterValue = filterRow.createCell(1);
				filterValue.setCellValue((String) pairs.getValue());

				rownumber = rownumber + 1;
				filterRow = sheet.createRow(rownumber);
			}

			rownumber = rownumber + 2;
			// /////////////////////////
			HSSFRow titleRow = sheet.createRow(rownumber);
			for (int i = 1; i <= count; i++) {
				HSSFCell cell = titleRow.createCell(i - 1);
				// cell.setCellStyle();
				// setting the column width
				// columnWidths[i - 1] = rsm.getColumnName(i).length();
				// System.out.println(rsm.getColumnName(i));
				Styles.addStyleAndValue(java.sql.Types.VARCHAR, rsm.getColumnName(i), cell);
				cell.setCellStyle(styles.get("title"));
			}
			rownumber++;

			while (rs.next()) {
				// creating next subsequent rows
				HSSFRow row = sheet.createRow(rownumber);
				// for setting the width of the column

				for (int cellnumber = 1; cellnumber <= count; cellnumber++) {

					// as cell starts with 0
					// for column widths
					HSSFCell cell = row.createCell(cellnumber - 1);
					// System.out.println(rs.getString(cellnumber));
					Styles.addStyleAndValue(rs.getMetaData().getColumnType(cellnumber), rs.getString(cellnumber), cell);
				}
				rownumber++;
			}// while loop closing brace

			// setting the width the columns
			/*
			 * for (int i = 0; i < columnWidths.length; i++) {
			 * sheet.setColumnWidth(i, columnWidths[i] * 400); }
			 */
			for (int i = 0; i < count; i++) {
				try {
					sheet.autoSizeColumn(i);
				} catch (Exception e) {
					// TODO: handle exception
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			log.debug("Error in exporting excel", e);
		}

		try {
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
			log.debug("Error in exporting excel while writing to workbook", e);
		} finally {

		}
	}

	/**
	 * For all SQL with group by statement
	 * 
	 * @param sql
	 */
	public void createGroupByExcel(String sql) {
		Map map = new HashMap();
		try {
			ResultSet rs = getJDBCConnection().createStatement().executeQuery(sql);
			ResultSetMetaData rsm = rs.getMetaData();
			int count = rsm.getColumnCount();
			// System.out.println(rsm.getTableName(1));
			// giving the sheet a name
			HSSFSheet sheet = workbook.createSheet(rsm.getTableName(1));
			// columnWidths = new int[count];
			// setting the title row
			HSSFRow titleRow = sheet.createRow(0);
			for (int i = 1; i <= count; i++) {
				HSSFCell cell = titleRow.createCell(i - 1);
				// cell.setCellStyle();
				// setting the column width
				// columnWidths[i - 1] = rsm.getColumnName(i).length();
				// System.out.println(rsm.getColumnName(i));
				Styles.addStyleAndValue(java.sql.Types.VARCHAR, rsm.getColumnName(i), cell);
				cell.setCellStyle(styles.get("title"));
			}

			int rowNumber = 1;
			int groupNumber = 1;
			String checkForGroup = "";
			int countGroup = 0;
			int numberOfMatches = 0;
			boolean status = false;
			while (rs.next()) {

				// creating next subsequent rows
				HSSFRow row = sheet.createRow(rowNumber);
				// storing the first column value to iterate as a group
				// setting the status value as false in the beginning of every
				// iteration
				status = false;
				if (checkForGroup.equalsIgnoreCase(rs.getString(1))) {
					countGroup++;
				} else {
					// in case the string doesn't match we get the string and
					// assign it and then assign the count value also back to
					// zero as we iterate using this value
					checkForGroup = rs.getString(1);
					numberOfMatches = countGroup;
					// this is case is checked for the first instance so that
					// below for loop doesn't run
					if (countGroup > 0) {
						status = true;
					}
					countGroup = 0;
				}
				// this if construct needs to work in case there is no match of
				// the assigned string to the retrieved string
				if (checkForGroup != null) {
					if (status) {
						// sheet.groupRow(groupNumber, groupNumber + 3);
						for (int i = groupNumber; i < groupNumber + numberOfMatches; i++) {

							// System.out.println("group number " + groupNumber
							// + " count value " + numberOfMatches);
							sheet.groupRow(groupNumber, i);

							// sheet.groupRow(groupNumber, groupNumber + 1);

							// sheet.groupRow(groupNumber, groupNumber + 2);

						}
						groupNumber = groupNumber + numberOfMatches + 1;
						// collapsing the group
						// sheet.setRowGroupCollapsed(5,true);
					}// if closing brace
				}
				for (int cellnumber = 1; cellnumber <= count; cellnumber++) {

					// as cell starts with 0
					// for column widths
					// int width = rs.getString(cellnumber).length();
					/*
					 * if (columnWidths[cellnumber - 1] < width) {
					 * columnWidths[cellnumber - 1] = width; }
					 */
					HSSFCell cell = row.createCell(cellnumber - 1);
					// System.out.println(rs.getString(cellnumber));
					Styles.addStyleAndValue(rs.getMetaData().getColumnType(cellnumber), rs.getString(cellnumber), cell);
				}
				rowNumber++;
			}// while loop closing brace

			/**
			 * for last match
			 */
			// //System.out.println("number Of Matches at end " +
			// numberOfMatches);
			// //System.out.println("group Number at end " + groupNumber);
			for (int i = groupNumber; i < groupNumber + numberOfMatches; i++) {

				// System.out.println("group number " + groupNumber +
				// " count value " + numberOfMatches);
				sheet.groupRow(groupNumber, i);

				// sheet.groupRow(groupNumber, groupNumber + 1);

				// sheet.groupRow(groupNumber, groupNumber + 2);

			}

			// setting the width the columns
			/*
			 * for (int i = 0; i < columnWidths.length; i++) {
			 * sheet.setColumnWidth(i, columnWidths[i] * 400); }
			 */

			for (int i = 0; i < count; i++) {
				sheet.autoSizeColumn(i);

			}

		} catch (SQLException e) {
			// TODO: handle exception
		}

		OutputStream fos = null;
		try {
			fos = new FileOutputStream("DemoDemo.xls");
			workbook.write(fos);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			try {
				fos.close();
			} catch (Exception e) {
				// TODO: handle exception
			}

		}
	}

	/**
	 * @param sql
	 */
	public void createGroupByExcelModified(String sql) {
		Map map = new HashMap();
		try {

			// first thing is to split the sql string and get to know the
			// grouped columns

			String groupedColumns[] = processGroupedColumns(sql);

			ResultSet rs = getJDBCConnection().createStatement().executeQuery(sql);
			ResultSetMetaData rsm = rs.getMetaData();
			int count = rsm.getColumnCount();
			// System.out.println(rsm.getTableName(1));
			// giving the sheet a name
			HSSFSheet sheet = workbook.createSheet(rsm.getTableName(1));
			// columnWidths = new int[count];
			// setting the title row
			HSSFRow titleRow = sheet.createRow(0);
			for (int i = 1; i <= count; i++) {
				HSSFCell cell = titleRow.createCell(i - 1);
				// cell.setCellStyle();
				// setting the column width
				// columnWidths[i - 1] = rsm.getColumnName(i).length();
				// System.out.println(rsm.getColumnName(i));
				Styles.addStyleAndValue(java.sql.Types.VARCHAR, rsm.getColumnName(i), cell);
				cell.setCellStyle(styles.get("title"));
			}

			// rows start with zero but as title is put its 1
			int rownumber = 1;
			int previousRowCount = 1;
			int groupedColumnsLength = groupedColumns.length;
			int goback = 0;
			while (rs.next()) {
				HSSFRow row = sheet.createRow(rownumber);
				for (int cellnumber = 1; cellnumber <= count; cellnumber++) {
					HSSFCell cell = row.createCell(cellnumber - 1);
					// System.out.println(rs.getString(cellnumber));
					Styles.addStyleAndValue(rs.getMetaData().getColumnType(cellnumber), rs.getString(cellnumber), cell);
				}
				int matchedRows = 0;
				int previousIterationCount = 0;
				// System.out.println("Index id " + rs.getString(1));
				if (rownumber == 103)
					break;
				for (int columnIndex = 0; columnIndex < groupedColumnsLength; columnIndex++) {
					// check whether column index is null
					// get the previous column value
					// go to previous row and check the match for previous
					// column
					if (rs.getString(groupedColumns[columnIndex]) == null) {
						// getting the previous column value
						// if(count-columnIndex!=0)
						// if previous column value == null then go to previous
						// column again
						String previousColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
						while (previousColumnValue == null) {
							previousColumnValue = rs.getString(groupedColumns[columnIndex - 1 + --goback]);
						}
						// going to previous row
						rs.previous();
						previousIterationCount++;
						String previousRowColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
						if (previousRowColumnValue != null & previousColumnValue != null) {
							while (previousColumnValue.equals(previousRowColumnValue)) {
								// if they are equal then go to the previous row
								// and
								// again do check for the same
								previousColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
								if (rs.previous()) {
									previousIterationCount++;
									previousRowColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
									// count the previous rows that matched
								} else {
									// come out of loop
									// for a single match
									// go forward
									// rs.next();
									// rs.next();
									matchedRows++;
									break;
								}
								// rs.next();
								matchedRows++;
							}// while loop
						}// if
						// the rows that have matched need to be grouped now
						// for (int i = rownumber; i > rownumber - matchedRows;
						// i--) {

						/*
						 * //System.out.println("group number " + rownumber + "
						 * count value " + matchedRows);
						 */
						// System.out.println("groupRow(" + (rownumber -
						// matchedRows) + "," + rownumber);
						sheet.groupRow(rownumber - matchedRows + 1, rownumber);
						// rs.next();
						// sheet.groupRow(groupNumber, groupNumber + 1);

						// sheet.groupRow(groupNumber, groupNumber + 2);

						// }
						// when the if gets true then the next iteration should
						// be broken thus break
						break;
					} else {
						previousRowCount++;
					}
				}
				rownumber++;
				/*
				 * if (previousIterationCount != 0) { for (int i = 0; i <
				 * previousIterationCount + 1; i++) { rs.next(); rs. } }
				 */
				rs.absolute(rownumber - 1);
			}

			rownumber = 1;

			/*
			 * while (rs.next()) { rs.absolute(1); // creating next subsequent
			 * rows HSSFRow row = sheet.createRow(rownumber); for (int
			 * cellnumber = 1; cellnumber <= count; cellnumber++) { HSSFCell
			 * cell = row.createCell(cellnumber - 1);
			 * //System.out.println(rs.getString(cellnumber));
			 * Styles.addStyleAndValue(rs.getMetaData().getColumnTypeName(
			 * cellnumber), rs.getString(cellnumber), cell); } rownumber++; }
			 */
			for (int i = 0; i < count; i++) {
				sheet.autoSizeColumn(i);

			}

		} catch (SQLException e) {
			// TODO: handle exception
		}

		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream("DemoDemo.xls");
			workbook.write(fos);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			try {
				fos.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}

		}
	}

	/**
	 * @param reportName
	 * @param rs
	 * @param groupedColumns
	 * @param parameters
	 * @param out
	 */
	public void processToExcel(String reportName, ResultSet rs, String[] groupedColumns, Map parameters,
			OutputStream out) {
		try {

			// first thing is to split the sql string and get to know the
			// grouped columns

			ResultSetMetaData rsm = rs.getMetaData();
			// System.out.println("Statement " + rs.getStatement().toString());
			int count = rsm.getColumnCount();
			// System.out.println(rsm.getTableName(1));
			// giving the sheet a name
			HSSFSheet sheet = workbook.createSheet(rsm.getTableName(1));
			// setting the title row
			int rownumber = 0;
			HSSFRow headRow = sheet.createRow(rownumber);
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$G$1"));
			HSSFCell titleCell = headRow.createCell(0);
			Styles.addStyleAndValue(java.sql.Types.VARCHAR, reportName, titleCell);
			titleCell.setCellStyle(styles.get("header"));
			// after adding increment row
			rownumber = rownumber + 4;
			// /////////////////////////

			HSSFRow filterRow = sheet.createRow(rownumber);
			int next = 0;
			Set parameterEntrySet = parameters.entrySet();
			for (Iterator iter = parameterEntrySet.iterator(); iter.hasNext();) {
				Map.Entry pairs = (Map.Entry) iter.next();

				HSSFCell filterName = filterRow.createCell(0);
				filterName.setCellValue((String) pairs.getKey());

				HSSFCell filterValue = filterRow.createCell(1);
				filterValue.setCellValue((String) pairs.getValue());

				rownumber = rownumber + 1;
				filterRow = sheet.createRow(rownumber);
			}
			rownumber = rownumber + 2;
			// /////////////////////////
			HSSFRow titleRow = sheet.createRow(rownumber);

			for (int i = 1; i <= count; i++) {
				HSSFCell cell = titleRow.createCell(i - 1);
				// System.out.println(rsm.getColumnName(i));
				Styles.addStyleAndValue(java.sql.Types.VARCHAR, rsm.getColumnName(i), cell);
				cell.setCellStyle(styles.get("title"));
			}
			rownumber++;

			int previousRowCount = 1;
			int groupedColumnsLength = groupedColumns.length;
			int goback = 0;

			while (rs.next()) {
				HSSFRow row = sheet.createRow(rownumber);
				for (int cellnumber = 1; cellnumber <= count; cellnumber++) {
					HSSFCell cell = row.createCell(cellnumber - 1);
					// System.out.println(rs.getString(cellnumber));
					Styles.addStyleAndValue(rs.getMetaData().getColumnType(cellnumber), rs.getString(cellnumber), cell);
				}
				int matchedRows = 0;
				int previousIterationCount = 0;
				// System.out.println("Index id " + rs.getString(1));
				if (rs.getString(1) == null)
					break;
				for (int columnIndex = 0; columnIndex < groupedColumnsLength; columnIndex++) {
					// check whether column index is null
					// get the previous column value
					// go to previous row and check the match for previous
					// column
					if (rs.getString(groupedColumns[columnIndex]) == null) {
						// getting the previous column value
						// if previous column value == null then go to previous
						// column again
						String previousColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
						while (previousColumnValue == null) {
							previousColumnValue = rs.getString(groupedColumns[columnIndex - 1 + --goback]);
						}
						// going to previous row
						rs.previous();
						previousIterationCount++;
						String previousRowColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
						if (previousRowColumnValue != null & previousColumnValue != null) {
							while (previousColumnValue.equals(previousRowColumnValue)) {
								// if they are equal then go to the previous row
								// and
								// again do check for the same
								previousColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
								if (rs.previous()) {
									previousIterationCount++;
									previousRowColumnValue = rs.getString(groupedColumns[columnIndex - 1]);
									// count the previous rows that matched
								} else {
									// come out of loop
									// for a single match
									matchedRows++;
									break;
								}
								// rs.next();
								matchedRows++;
							}// while loop
						}// if

						// System.out.println("groupRow(" + (rownumber -
						// matchedRows) + "," + rownumber);
						sheet.groupRow(rownumber - matchedRows + 1, rownumber);
						// when the if gets true then the next iteration should
						// be broken thus break
						break;
					} else {
						previousRowCount++;
					}
				}
				rownumber++;
				rs.absolute(rownumber - 1);
			}

			rownumber = 1;

			for (int i = 0; i < count; i++) {
				sheet.autoSizeColumn(i);

			}

		} catch (SQLException e) {
			// TODO: handle exception
		}

		try {
			workbook.write(out);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {

		}

	}

	/**
	 * @param reportName
	 * @param sql
	 * @param parameters
	 */
	public void generateReport(String reportName, String sql, Map<String, Object> parameters) {
		try {
			OutputStream out = new FileOutputStream(reportName + ".xls");
			generateReport(reportName, sql, parameters, out);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @param reportName
	 * @param sql
	 * @param parameters
	 * @param out
	 */
	public void generateReport(String reportName, String sql, Map<String, Object> parameters, OutputStream out) {
		// getting the column names to which the group by clause has been
		// attached
		String[] groupedColumns = processGroupedColumns(sql);
		try {
			PreparedStatement pstmt = buildQuery(sql, parameters);
			ResultSet rs = pstmt.executeQuery();
			if (groupedColumns == null) {
				createExcel(reportName, rs, parameters, out);
			} else {
				processToExcel(reportName, rs, groupedColumns, parameters, out);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	/**
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public PreparedStatement buildQuery(String sql, Map<String, Object> parameters) {
		PreparedStatement pstmt = null;
		try {
			pstmt = getJDBCConnection().prepareStatement(sql);

			Set set = parameters.keySet();
			for (Iterator ite = set.iterator(); ite.hasNext();) {
				// System.out.println("Set values " + ite.next());
			}

			int numberOfParams = parameters.size();
			Iterator iter = set.iterator();
			int i = 1;
			while (iter.hasNext()) {
				String key = (String) iter.next();
				Object value = parameters.get(key);
				pstmt.setObject(i, value);
				i++;
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return pstmt;
	}

	/**
	 * @param sql
	 * @return
	 */
	public String[] processGroupedColumns(String sql) {

		String regex = " GROUP BY ";
		Pattern pattern = Pattern.compile(regex);
		// System.out.println(sql);

		String caseChanged = sql.toUpperCase();

		Matcher matcher = pattern.matcher(caseChanged);

		int count1 = 0;
		int count2 = 0;
		while (matcher.find()) {
			count1 = matcher.end();
		}
		String withoutGroupBy = caseChanged.substring(count1).trim();

		regex = " WITH ROLLUP\b";
		pattern = Pattern.compile(regex);
		matcher = pattern.matcher(withoutGroupBy);

		count2 = 0;
		while (matcher.find()) {
			count2 = matcher.start();
		}
		String groupedColumns[] = null;
		if (count2 != 0 && count1 != 0) {
			groupedColumns = (withoutGroupBy.substring(0, count2).trim()).split(","); // return
			// groupedColumns;
		} else if (count1 != 0) {
			groupedColumns = withoutGroupBy.split(",");
		}
		return groupedColumns;
	}

	/**
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		ExcelExporter excel = new ExcelExporter();
		// excel.createGroupByExcel("SELECT * FROM Address");
		/*
		 * excel
		 * .createGroupByExcelModified("SELECT DATE_DIM_ID,COVERAGE_DIM_ID," +
		 * "LOB_DIM_ID,JURISDICTION_DIM_ID,SUM(AVG_ALE) FROM " +
		 * "insight.rpt_fact_claims_metrics GROUP BY DATE_DIM_ID," +
		 * "COVERAGE_DIM_ID,LOB_DIM_ID,JURISDICTION_DIM_ID WITH ROLLUP");
		 */

		Map<String, Object> map = new LinkedHashMap<String, Object>();
		// map.put("city", "Hyderabad");
		// map.put("Country", "india");
		// map.put("DATE_DIM_ID", 1);
		// String
		// sql="SELECT DATE_DIM_ID,COVERAGE_DIM_ID,LOB_DIM_ID,JURISDICTION_DIM_ID,SUM(AVG_ALE) FROM insight.rpt_fact_claims_metrics WHERE DATE_DIM_ID= ? GROUP BY DATE_DIM_ID,COVERAGE_DIM_ID,LOB_DIM_ID,JURISDICTION_DIM_ID WITH ROLLUP";
		String sql = "SELECT * FROM COUNTRIES WHERE COUNTRY_NAME = 'india'";
		// excel.buildTable("Population",
		// "select * from address where city =? and country=? GROUP BY city",map);
		OutputStream out = new FileOutputStream("c:\\test.xls");
		excel.generateReport("Insight", sql, map, out);
	}
}
